Access chained data from MySQL
I have a MySQL table which is as follows:
id | name | parent_id
19 | category1 | 0
20 | category2 | 19
21 | category3 | 20
22 | category4 | 21
Here id 19 is the parent entry. And id with value 20, 21 and 22 are its children. So it will look like
19
|
20
|
21
|
22
Now I need to select all the childs of the given parent. ie If I input 19 the output should be
(20, ‘category2′, 19),
(21, ‘category3′, 20),
(22, ‘category4′, 21),
and for the input 30, output should be
(31, ‘category6′, 30),
(32, ‘category7′, 31),
So here is the create table query
Create table query:
CREATE TABLE category (
category_id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
parent int(11) DEFAULT NULL,
PRIMARY KEY (category_id)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1
Sample data:
INSERT INTO category (category_id, name, parent) VALUES
(19, ‘category1′, 0),
(20, ‘category2′, 19),
(21, ‘category3′, 20),
(22, ‘category4′, 21),
(30, ‘category5′, 0),
(31, ‘category6′, 30),
(32, ‘category7′, 31);
The solution:
SELECT @pv := category_id AS category_id, name, parent
FROM category
JOIN (
SELECT @pv :=19
)tmp
WHERE parent = @pv ;